/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package edu.uta.tsrh.patient.misc;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author David
 */
public class SQLMgr implements DBMgr {

    private Connection connection = null;

    public SQLMgr() {
        openDB();
    }

    private void openDB() {
        if (this.connection == null) {
            SQLConnMgr sqlConnection = new SQLConnMgr();
            connection = sqlConnection.getConnection();
        }
    }

    @Override
    public int addPatientNote(String patientId, PatientNote note) {
        PreparedStatement statement;
        int newId = 0;
        try {
            statement = connection.prepareStatement(
                    "INSERT INTO PatientNote ("
                    + "patientId,"
                    + "date,"
                    + "title,"
                    + "source,"
                    + "content"
                    + ") "
                    + "VALUES (?,?,?,?,?)", Statement.RETURN_GENERATED_KEYS);
            statement.setString(1, patientId);
            statement.setTimestamp(2, note.getDate());
            statement.setString(3, note.getTitle());
            statement.setString(4, note.getSource());
            statement.setString(5, note.getContent());

            statement.executeUpdate();

            ResultSet keys = statement.getGeneratedKeys();
            if (keys.next()) {
                newId = keys.getInt(1);
            }

        } catch (SQLException ex) {
            Logger.getLogger(SQLMgr.class.getName()).log(Level.SEVERE, null, ex);
        }
        return newId;
    }

    @Override
    public void editPatientNote(PatientNote note) {
        Statement statement;
        try {
            statement = connection.prepareStatement(
                    "UPDATE PatientNote SET "
                    + "title = ?, "
                    + "content = ? "
                    + "WHERE "
                    + "id = ?");
            ((PreparedStatement) statement).setString(1, note.getTitle());
            ((PreparedStatement) statement).setString(2, note.getContent());
            ((PreparedStatement) statement).setInt(3, note.getId());

            ((PreparedStatement) statement).executeUpdate();

        } catch (SQLException ex) {
            Logger.getLogger(SQLMgr.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    @Override
    public int addPatientPhoneCall(String patientId, PatientPhoneCall call) {
        PreparedStatement statement;
        int newId = 0;
        try {
            statement = connection.prepareStatement(
                    "INSERT INTO PatientPhoneCall ("
                    + "patientId,"
                    + "title,"
                    + "date,"
                    + "caller,"
                    + "sourceNumber,"
                    + "content"
                    + ") "
                    + "VALUES (?,?,?,?,?,?)", Statement.RETURN_GENERATED_KEYS);
            statement.setString(1, patientId);
            statement.setString(2, call.getTitle());
            statement.setTimestamp(3, call.getDate());
            statement.setString(4, call.getCaller());
            statement.setString(5, call.getSourceNumber());
            statement.setString(6, call.getContent());

            statement.executeUpdate();

            ResultSet keys = statement.getGeneratedKeys();
            if (keys.next()) {
                newId = keys.getInt(1);
            }



        } catch (SQLException ex) {
            Logger.getLogger(SQLMgr.class.getName()).log(Level.SEVERE, null, ex);
        }
        return newId;
    }

    @Override
    public void hidePatientNote(int noteId) {
        Statement statement;
        try {
            statement = connection.prepareStatement("UPDATE PatientNote SET hidden = 1 WHERE "
                    + "id = ?");
            ((PreparedStatement) statement).setInt(1, noteId);
            ((PreparedStatement) statement).executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(SQLMgr.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    @Override
    public void hidePatientPhoneCall(int callId) {
        Statement statement;
        try {
            statement = connection.prepareStatement("UPDATE PatientPhoneCall SET hidden = 1 WHERE "
                    + "id = ?");
            ((PreparedStatement) statement).setInt(1, callId);
            ((PreparedStatement) statement).executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(SQLMgr.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    @Override
    public String getNewId(String firstName, String lastName) {
        Statement statement;
        try {


            String fL = String.valueOf(firstName.charAt(0));
            String lL = String.valueOf(lastName.charAt(0));
            statement = connection.prepareStatement("SELECT patientId FROM PatientRecord WHERE "
                    + "patientId LIKE \"" + fL + lL + "%\" ORDER BY patientId DESC");

            ResultSet results = ((PreparedStatement) statement).executeQuery();

            Integer max = 0;
            while (results.next()) {
                String lastId = results.getString("patientId");
                Integer number = Integer.parseInt(lastId.substring(2));
                if (number > max) {
                    max = number;
                }
            }
            max++;
            return fL + lL + max;

        } catch (SQLException ex) {
            Logger.getLogger(SQLMgr.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    @Override
    public ArrayList<PatientRecord> getPatientRecordList(String patientId, String patientFirstName, String patientLastName, Date patientDateOfBirth) {
        ArrayList<PatientRecord> newPRList = new ArrayList<PatientRecord>();

        ResultSet results;
        try {
            results = getPatientRecordResults(patientId, patientFirstName, patientLastName, patientDateOfBirth);


            while (results.next()) {

                PatientRecord newPR = new PatientRecord(
                        results.getString("firstName"),
                        results.getString("lastName"),
                        results.getString("patientId"),
                        results.getDate("dateofbirth"),
                        results.getString("phonenumber"),
                        results.getString("address"),
                        results.getString("mothersName"),
                        results.getString("photolink"),
                        results.getString("pdflink"));
                newPRList.add(newPR);
            }
        } catch (SQLException ex) {
            Logger.getLogger(SQLMgr.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return newPRList;
    }
        
    /**
     * Get patient record by patient id
     *
     * @param patientId
     * @return
     */
    @Override
    public PatientRecord getPatientRecord(String patientId) {

        PatientRecord newPR = null;
        try {

            ResultSet results = getSinglePatientRecord(patientId);

            while (results.next()) {

                newPR = new PatientRecord(
                        results.getString("firstName"),
                        results.getString("lastName"),
                        results.getString("patientId"),
                        results.getDate("dateofbirth"),
                        results.getString("phonenumber"),
                        results.getString("address"),
                        results.getString("mothersName"),
                        results.getString("photolink"),
                        results.getString("pdflink"));
            }

            results = getPatientPhoneCalls(newPR.getId());

            while (results.next()) {

                PatientPhoneCall newPC = new PatientPhoneCall(
                        results.getInt("id"),
                        results.getString("title"),
                        results.getTimestamp("date"),
                        results.getString("caller"),
                        results.getString("sourceNumber"),
                        results.getString("content"));
                newPR.addPhoneCall(newPC);
            }

            results = getPatientNotes(newPR.getId());

            while (results.next()) {

                PatientNote newPN = new PatientNote(
                        results.getInt("id"),
                        results.getTimestamp("date"),
                        results.getString("title"),
                        results.getString("source"),
                        results.getString("content"));
                newPR.addNote(newPN);
            }



        } catch (SQLException ex) {
            Logger.getLogger(SQLMgr.class.getName()).log(Level.SEVERE, null, ex);
        }
        return newPR;
    }

    private ResultSet getPatientRecordResults(String patientId, String patientFirstName, String patientLastName, Date DOB) throws SQLException {
        String sqlQuery = buildQuery(patientId, patientFirstName, patientLastName, DOB);
        Statement statement = connection.prepareStatement(sqlQuery);
        ResultSet results = ((PreparedStatement) statement).executeQuery();
        return results;
    }
    
   private ResultSet getSinglePatientRecord(String patientId) throws SQLException {
        String sqlQuery = "select * from patientrecord where patientId = '" + patientId + "'";
        Statement statement = connection.prepareStatement(sqlQuery);
        ResultSet results = ((PreparedStatement) statement).executeQuery();
        return results;
    }

    private ResultSet getPatientPhoneCalls(String patientId) throws SQLException {
        Statement statement = connection.prepareStatement("SELECT * FROM PatientPhoneCall WHERE "
                + "patientId = ? AND hidden = 0");
        ((PreparedStatement) statement).setString(1, patientId);
        ResultSet results = ((PreparedStatement) statement).executeQuery();
        return results;
    }

    private ResultSet getPatientNotes(String patientId) throws SQLException {
        Statement statement = connection.prepareStatement("SELECT * FROM PatientNote WHERE "
                + "patientId = ? AND hidden = 0 order by date desc");
        ((PreparedStatement) statement).setString(1, patientId);
        ResultSet results = ((PreparedStatement) statement).executeQuery();
        return results;
    }

    private Address getAddress(int addressId) {
        return null;
    }

    /**
     * Get patient phone call by call id
     *
     * @param callId
     * @return
     */
    @Override
    public PatientPhoneCall getPatientPhoneCall(int callId) {
        PatientPhoneCall newPC = null;
        try {
            ResultSet results = getPatientPhoneCallResults(callId);

            while (results.next()) {

                newPC = new PatientPhoneCall(
                        results.getInt("id"),
                        results.getString("title"),
                        results.getTimestamp("date"),
                        results.getString("caller"),
                        results.getString("sourceNumber"),
                        results.getString("content"));
            }


        } catch (SQLException ex) {
            Logger.getLogger(SQLMgr.class.getName()).log(Level.SEVERE, null, ex);
        }


        return newPC;

    }

    private ResultSet getPatientPhoneCallResults(int callId) throws SQLException {
        Statement statement = connection.prepareStatement("SELECT * FROM PatientPhoneCall WHERE "
                + "id = ?");
        ((PreparedStatement) statement).setInt(1, callId);
        ResultSet results = ((PreparedStatement) statement).executeQuery();
        return results;
    }

    @Override
    public PatientNote getPatientNote(int noteId) {

        PatientNote newPN = null;
        try {
            ResultSet results = getPatientNoteResults(noteId);

            while (results.next()) {

                newPN = new PatientNote(
                        results.getInt("id"),
                        results.getTimestamp("date"),
                        results.getString("title"),
                        results.getString("source"),
                        results.getString("content"));
            }


        } catch (SQLException ex) {
            Logger.getLogger(SQLMgr.class.getName()).log(Level.SEVERE, null, ex);
        }


        return newPN;
    }

    private ResultSet getPatientNoteResults(int noteId) throws SQLException {
        Statement statement = connection.prepareStatement("SELECT * FROM PatientNote WHERE "
                + "id = ?");
        ((PreparedStatement) statement).setInt(1, noteId);
        ResultSet results = ((PreparedStatement) statement).executeQuery();
        return results;
    }

    @Override
    public boolean ready() {
        return connection != null;
    }

    private String buildQuery(String patientID, String patientFirstName, String patientLastName, Date DOB) {

        String sqlQuery = "select * from";
        
        if(patientLastName.equals(" ") && patientFirstName.equals(" ") && (DOB == null) && patientID.equals("")) {
            sqlQuery += " patientrecord";
            return sqlQuery;
        }
        
        sqlQuery += " patientrecord p where ";

        boolean addedfirst = false;

        if (!patientID.isEmpty()) {

            sqlQuery += "p.patientId like '%" + patientID + "%'";
            addedfirst = true;
        }

        if (patientFirstName.equals(" ") == false) {

            if (addedfirst) {
                sqlQuery += " or ";
            }
            if (patientFirstName.isEmpty()) {
                sqlQuery += "p.firstname like '%'";
            } else {
                sqlQuery += "p.firstname like '%" + patientFirstName + "%'";
            }
            addedfirst = true;
        }


        if (patientLastName.equals(" ") == false) {

            if (addedfirst) {
                sqlQuery += " or ";
            }
            if (patientFirstName.isEmpty()) {
                sqlQuery += "p.lastname like '%'";
            } else {
                sqlQuery += "p.lastname like '%" + patientLastName + "%'";
            }
            addedfirst = true;
        }


        if (DOB != null) {
            if (addedfirst) {
                sqlQuery += " or ";
            }

            sqlQuery += "p.dateofbirth = '" + DOB + "'";
        }

        return sqlQuery;
    }

    @Override
    public int  addNewPatientRecord(PatientRecord pRecord) {
         int value=0;
         PreparedStatement statement;
         try {
            statement = connection.prepareStatement(
                    "INSERT INTO patientrecord ("
                    + "lastname,"
                    + "firstname,"
                    + "patientId,"
                    + "dateofbirth,"
                    + "phonenumber,"
                    + "address,"
                    + "mothersname,"
                    + "photolink,"
                    + "pdflink"
                    + ") "
                    + "VALUES (?,?,?,?,?,?,?,?,?)",Statement.SUCCESS_NO_INFO);
            statement.setString(1, pRecord.getLastName());
            statement.setString(2, pRecord.getFirstName());
            statement.setString(3, pRecord.getId());
            statement.setDate(4, pRecord.getDOB());
            statement.setString(5, pRecord.getContactPhone());
            statement.setString(6,pRecord.getAddress());
            statement.setString(7, pRecord.getMothersName());
            statement.setString(8, pRecord.getURL().toString());
            statement.setString(9, pRecord.getpdfURL().toString());
             value = statement.executeUpdate();
                             
             System.out.println("No of rows affected:"  +value);
           } catch (SQLException ex) {
            Logger.getLogger(SQLMgr.class.getName()).log(Level.SEVERE, null, ex);
            System.err.println("SQLState: " +
                    ((SQLException)ex).getSQLState());
             System.err.println("Error Code: " +
                    ((SQLException)ex).getErrorCode());
              System.err.println("Message: " + ex.getMessage());
           int errorCode = Integer.parseInt(((SQLException)ex).getSQLState());
         if(errorCode==23000)
{
   value = 0;
}
           System.out.println("ErrorCode is " +errorCode);
        }
        return value;
    }


    @Override
    public boolean checkPatientAvailibity(String patientId) {
        System.out.print("patient id in sql impl "+ patientId);
                       boolean status = false;
                       
        PreparedStatement statement ;
        try {
            int value=0;
            ResultSet result;
            statement = connection.prepareStatement("SELECT count(*) from patientrecord WHERE patientId = ?");
            statement.setString(1, patientId);
            
            System.out.println(statement);
            result = statement.executeQuery();
            while (result.next()) {
                value= result.getInt("count(*)");
            }
            System.out.println("value inside try " + value);
            if (value==0){status=true;}
            else if(value==1){status=false;}
        } catch (SQLException ex) {
            Logger.getLogger(SQLMgr.class.getName()).log(Level.SEVERE, null, ex);
        }
        System.out.print("status in sql impl "+ status);
        return status;
    }

    @Override
    public void setPhysicalFileRecord(String location, String fileStatus, String fileID, String timeStamp) {
       PreparedStatement statement;
         try {
            statement = connection.prepareStatement(
                    "INSERT INTO physical_file ("
                    + "location,"
                    + "file_state,"
                    + "file_id,"
                    + "created_date"
                    + ") "
                    + "VALUES (?,?,?,?)");
            statement.setString(1, location);
            statement.setString(2, fileStatus);
            statement.setString(3, fileID);
            statement.setString(4, timeStamp);
            System.out.println(statement);
            statement.executeUpdate();
           } catch (SQLException ex) {
            Logger.getLogger(SQLMgr.class.getName()).log(Level.SEVERE, null, ex);
        }
        
    }



    
}
